package com.sena.crawler.run;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.sena.crawler.util.JedisUtilNew;
import com.sena.crawler.util.ReadProperties;
import redis.clients.jedis.ShardedJedis;

import java.net.URL;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;

public class RunInsertSenaLinkScanRecur {
    public static void main(String[] args) {
        insertWlinkData();
    }

    public static void del() {
        JedisUtilNew jedisUtil = new JedisUtilNew(ReadProperties.getRedisUrlValue("url"));
        ShardedJedis client = jedisUtil.getInstance();
        Map<String, String> map = client.hgetAll("OUTER_LINK");
        for (Map.Entry<String, String> entry : map.entrySet()) {
            if (entry.getValue().toString().contains("\\\"cnt_wlink\\\":240")) {
                client.hdel("OUTER_LINK", entry.getKey());
                System.out.println(entry.getValue().toString());
            }
        }
    }

    public static void insertUrlData() {
        //初始化url
        JedisUtilNew jedisUtil = new JedisUtilNew(ReadProperties.getRedisUrlValue("url"));
        ShardedJedis client = jedisUtil.getInstance();

        Map<String, String> map = client.hgetAll("OTHER_LINK");

        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");//设置日期格式
        String now = df.format(new Date());
        String url = ReadProperties.getJDBCUrlValue("url");
        String sql_user = ReadProperties.getJDBCUrlValue("username");
        String sql_pwd = ReadProperties.getJDBCUrlValue("password");

        int count = 0;
        List<String> addList = new ArrayList<String>();
        for (Map.Entry<String, String> entry : map.entrySet()) {
            //System.out.println("Key = " + entry.getKey() + ", Value = " + entry.getValue());
            JSONObject urlVo = JSON.parseObject(entry.getValue());
            JSONArray cntWlinks = JSONArray.parseArray(urlVo.getString("cntWlinks"));
            String wlinks = "";
            for (int i = 0; i < cntWlinks.size(); i++) {
                wlinks += cntWlinks.get(i) + "\n";
            }

            //System.out.println("wlinks=="+wlinks);
            String tempurl = "";
            if (urlVo.get("scanUrl") != null && urlVo.get("scanUrl").toString().indexOf("<") > 0) {  //都有尖括号的过滤
                int pos = urlVo.get("scanUrl").toString().indexOf("<");
                tempurl = urlVo.get("scanUrl").toString().substring(0, pos);
                // System.out.println("tempurl===="+tempurl);
            } else
                tempurl = urlVo.get("scanUrl").toString();
            String insertSql = "insert into sena_link_scan_recur(res_id,scan_url,scan_time,wlinks,cnt_wlink) values(1,'" + tempurl.replaceAll("'", "") + "','" + now + "','" + wlinks + "'," + urlVo.getString("cntWlink") + ");";
            //System.out.println(insertSql);
            Connection conn = null;
            Statement stmt = null;


            addList.add(insertSql);
            //  System.out.println(insertSql);
            count++;
            if (addList.size() >= 2000 || count == map.entrySet().size()) { //每1000条插入一次
                try {
                    Class.forName("com.mysql.cj.jdbc.Driver");
                    conn = DriverManager.getConnection(url, sql_user, sql_pwd);
                    stmt = conn.createStatement();
                    conn.setAutoCommit(false);
                    stmt = conn.createStatement();

                    for (int i = 0; i < addList.size(); i++) {
                        stmt.addBatch(addList.get(i).toString());
                    }
                    stmt.executeBatch();
                    conn.commit();

                    System.out.println("================成功插入" + count + "条");
                } catch (Exception e) {
                    e.printStackTrace();
                } finally {
                    addList.clear();
                    if (conn != null) {
                        try {
                            conn.close();
                            //System.out.println ("Database connection terminated");
                        } catch (Exception e) {
                        }
                    }
                    if (stmt != null) {
                        try {
                            stmt.close();
                            //System.out.println ("Database connection terminated");
                        } catch (Exception e) {
                        }
                    }
                }

            }
        }


        System.out.println("map==" + map.size());


    }


    public static void insertWlinkData() {
        JedisUtilNew jedisUtilNew = new JedisUtilNew(ReadProperties.getRedisUrlValue("url"));
        ShardedJedis client = jedisUtilNew.getInstance();
        Map<String, String> map = client.hgetAll("OUTER_LINK");

        HashMap<String, Integer> resourceMap = getResourceWsName();
        Set<String> set = getWhiteKey();
        List<String> addList = new ArrayList<String>();

        //if (resourceMap.size() > 0) {
            for (Map.Entry<String, String> entry : map.entrySet()) {
                int num = 0;
                String data = entry.getValue().replaceAll("\\\\", "");
                int i = data.lastIndexOf("{");
                int j = data.lastIndexOf("}");
                String sub = data.substring(i, j + 1);
                JSONObject urlVo = JSON.parseObject(sub);
                for (Map.Entry<String, Integer> resource : resourceMap.entrySet()) {
                    if (urlVo.getString("scan_url").contains(resource.getKey())) {
                        num += 1;
                        JSONArray KList = JSONArray.parseArray(urlVo.getString("wlinks"));
                        String wlinks = "";
                        int count = 0;
                        if (KList != null) {
                            int cnt = 0;
                            for (int k = 0; k < KList.size(); k++) {
                                for (String whiteKey : set) {
                                    if (KList.get(k).toString().contains(whiteKey)) {
                                        cnt += 1;
                                    }
                                }
                                if (cnt == 0) {
                                    count += 1;
                                    wlinks += KList.get(k) + "\n";
                                }
                            }

                        }
                        if (count > 0) {
                            String url_str = urlVo.getString("scan_url").replaceAll("'", "");
                            url_str = url_str.replaceAll("%", "");
                            String insertSql = "insert into sena_link_scan_recur(res_id,scan_url,scan_time,wlinks,cnt_wlink) values(" + resource.getValue() + ",'" + url_str + "','" + getYearMonthDayHourMinuteSecond(urlVo.getLong("scan_time")) + "','" + wlinks.replaceAll("'", "") + "'," + count + ");";
                            addList.add(insertSql);
                        }
                    }

                }
                if (num == 0) {
                    String wlinks = "";
                    JSONArray KList = JSONArray.parseArray(urlVo.getString("wlinks"));
                    int count = 0;
                    if (KList != null) {
                        int cnt = 0;
                        for (int k = 0; k < KList.size(); k++) {
                            for (String whiteKey : set) {
                                if (KList.get(k).toString().contains(whiteKey)) {
                                    cnt += 1;
                                }
                            }
                            if (cnt == 0) {
                                count += 1;
                                wlinks += KList.get(k) + "\n";
                            }
                        }
                    }
                    if (count > 0) {
                        String url_str = urlVo.getString("scan_url").replaceAll("'", "");
                        url_str = url_str.replaceAll("%", "");

                        String insertSql = "insert into sena_link_scan_recur(res_id,scan_url,scan_time,wlinks,cnt_wlink) values(" + 0 + ",'" + url_str + "','" + getYearMonthDayHourMinuteSecond(urlVo.getLong("scan_time")) + "','" + wlinks.replaceAll("'", "") + "'," + count + ");";
                        addList.add(insertSql);
                    }
                }

            }
            groupWlinkList(addList);//批量插入
            System.out.println("数据插入完成");

        //}

    }

    public static int groupWlinkList(List<String> list) {
        String url = ReadProperties.getJDBCUrlValue("url");
        String sql_user = ReadProperties.getJDBCUrlValue("username");
        String sql_pwd = ReadProperties.getJDBCUrlValue("password");
        int listSize = list.size();
        int toIndex = 1000;
        int result = 0;
        for (int i = 0; i < list.size(); i += 1000) {
            if (i + 1000 > listSize) { // 作用为toIndex最后没有100条数据则剩余几条newList中就装几条
                toIndex = listSize - i;
            }
            List<String> newList = list.subList(i, i + toIndex);
            Connection conn = null;
            Statement stmt = null;
            try {
                Class.forName("com.mysql.cj.jdbc.Driver");
                conn = DriverManager.getConnection(url, sql_user, sql_pwd);
                stmt = conn.createStatement();
                conn.setAutoCommit(false);
                stmt = conn.createStatement();

                for (int f = 0; f < newList.size(); f++) {
                    stmt.addBatch(newList.get(f).toString());
                }
                stmt.executeBatch();
                conn.commit();

                System.out.println("================成功插入外链链接" + (i + toIndex) + "条");
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (conn != null) {
                    try {
                        conn.close();
                        //System.out.println ("Database connection terminated");
                    } catch (Exception e) {
                    }
                }
                if (stmt != null) {
                    try {
                        stmt.close();
                        //System.out.println ("Database connection terminated");
                    } catch (Exception e) {
                    }
                }
            }
        }
        return result;
    }
    public static void insertRecurData(Integer res_id, String scan_url, String scan_time, String wlinks, Integer cnt_wlink) {
        String url = ReadProperties.getJDBCUrlValue("url");
        String sql_user = ReadProperties.getJDBCUrlValue("username");
        String sql_pwd = ReadProperties.getJDBCUrlValue("password");
        String insertSql = "insert into sena_link_scan_recur(res_id,scan_url,scan_time,wlinks,cnt_wlink) values(" + res_id + ",'" + scan_url + "','" + scan_time + "','" + wlinks + "'," + cnt_wlink + ");";
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection(url, sql_user, sql_pwd);
            pstmt = conn.prepareStatement(insertSql, Statement.RETURN_GENERATED_KEYS);
            pstmt.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                    //System.out.println ("Database connection terminated");
                } catch (Exception e) {
                }
            }
            if (pstmt != null) {
                try {
                    pstmt.close();
                    //System.out.println ("Database connection terminated");
                } catch (Exception e) {
                }
            }
        }
    }

    public static Set<String> getWhiteKey() {
        {
            String url = ReadProperties.getJDBCUrlValue("url");
            String sql_user = ReadProperties.getJDBCUrlValue("username");
            String sql_pwd = ReadProperties.getJDBCUrlValue("password");
            String querySql = "select *from sena_white_list_config";
            Set<String> set = new HashSet<String>();
            Connection conn = null;
            Statement stmt = null;
            ResultSet rs = null;
            try {
                Class.forName("com.mysql.cj.jdbc.Driver");
                conn = DriverManager.getConnection(url, sql_user, sql_pwd);
                stmt = conn.createStatement();
                rs = stmt.executeQuery(querySql);
                while (rs.next()) {
                    set.add(rs.getString("site_content"));
                }

            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (conn != null) {
                    try {
                        conn.close();
                    } catch (Exception e) {
                    }
                }
                if (stmt != null) {
                    try {
                        stmt.close();
                    } catch (Exception e) {
                    }
                }
            }
            return set;
        }
    }

    public static HashMap<String, Integer> getResourceWsName() {
        String url = ReadProperties.getJDBCUrlValue("url");
        String sql_user = ReadProperties.getJDBCUrlValue("username");
        String sql_pwd = ReadProperties.getJDBCUrlValue("password");
        String querySql = "select *from sena_resource where Id>3";
        HashMap<String, Integer> map = new HashMap<String, Integer>();
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection(url, sql_user, sql_pwd);
            stmt = conn.createStatement();
            rs = stmt.executeQuery(querySql);
            while (rs.next()) {
                URL domain = new URL(rs.getString("ws_url"));
                map.put(domain.getHost(), rs.getInt("Id"));
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (Exception e) {
                }
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (Exception e) {
                }
            }
        }
        return map;
    }

    /**
     * 将毫秒转换为年月日时分秒
     *
     * @author GaoHuanjie
     */
    public static String getYearMonthDayHourMinuteSecond(long timeMillis) {
        Calendar calendar = Calendar.getInstance(TimeZone.getTimeZone("GMT+8"));
        calendar.setTimeInMillis(timeMillis);
        int year = calendar.get(Calendar.YEAR);

        int month = calendar.get(Calendar.MONTH) + 1;
        String mToMonth = null;
        if (String.valueOf(month).length() == 1) {
            mToMonth = "0" + month;
        } else {
            mToMonth = String.valueOf(month);
        }

        int day = calendar.get(Calendar.DAY_OF_MONTH);
        String dToDay = null;
        if (String.valueOf(day).length() == 1) {
            dToDay = "0" + day;
        } else {
            dToDay = String.valueOf(day);
        }

        int hour = calendar.get(Calendar.HOUR_OF_DAY);
        String hToHour = null;
        if (String.valueOf(hour).length() == 1) {
            hToHour = "0" + hour;
        } else {
            hToHour = String.valueOf(hour);
        }

        int minute = calendar.get(Calendar.MINUTE);
        String mToMinute = null;
        if (String.valueOf(minute).length() == 1) {
            mToMinute = "0" + minute;
        } else {
            mToMinute = String.valueOf(minute);
        }

        int second = calendar.get(Calendar.SECOND);
        String sToSecond = null;
        if (String.valueOf(second).length() == 1) {
            sToSecond = "0" + second;
        } else {
            sToSecond = String.valueOf(second);
        }
        return year + "-" + mToMonth + "-" + dToDay + " " + hToHour + ":" + mToMinute + ":" + sToSecond;
    }

}
